import os
import pandas as pd
import xlrd
from datetime import datetime
from xlrd import xldate_as_tuple
import re
from sql_con import *
from xlrd import xldate_as_tuple
import datetime

def handle_date(date):
    """
    将excel传入的变成float类型的日期信息转换为元组
    :param date:
    :return:
    """
    tuple = xldate_as_tuple(date, 0)
    return tuple

def create_store_item_by_excel():
    """
    根据excel创造固定资产条目（store.xlsx）
    """
    if os.path.exists("store.xlsx"):
        data = xlrd.open_workbook('store.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'type': table.cell_value(row, 1),
                     'brand': table.cell_value(row, 2), 'Classified_level': table.cell_value(row, 3),
                     'remarks': table.cell_value(row, 4), 'department': table.cell_value(row, 5),
                     'address': table.cell_value(row, 6), 'user': table.cell_value(row, 7),
                     'serial_number': table.cell_value(row, 8), 'VID': table.cell_value(row, 9),
                     'PID': table.cell_value(row, 10), 'Condition_now': table.cell_value(row, 11)
                     }
            tables.append(array)
        tables.pop(0)
        for i in tables:
            create_general_sheet_item(type=i['type'], brand=i['brand'], Asset_Number=i['Asset_Number'],
                                      Classified_level=i['Classified_level'], remarks=i['remarks'],
                                      department=i['department'],
                                      address=i['address'], user=i['user'], serial_number=i['serial_number'],
                                      VID=i['VID'],
                                      PID=i['PID'], Condition_now=i['Condition_now'])
        os.remove("store.xlsx")
def create_computer_item_by_excel():
    """
    根据excel创造固定资产条目（computer.xlsx）
    """
    if os.path.exists("computer.xlsx"):
        data = xlrd.open_workbook('computer.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'type': table.cell_value(row, 1),
                     'brand': table.cell_value(row, 2), 'Classified_level': table.cell_value(row, 3),
                     'remarks': table.cell_value(row, 4), 'department': table.cell_value(row, 5),
                     'address': table.cell_value(row, 6), 'user': table.cell_value(row, 7),
                     'Hard_disk': table.cell_value(row, 8), 'operating_system': table.cell_value(row, 9),
                     'IP_address': table.cell_value(row, 10), 'Wired_MAC_address': table.cell_value(row, 11),
                     'CD_drive': table.cell_value(row, 12), 'Purchase_date': table.cell_value(row, 13),
                     'Condition_now': table.cell_value(row, 14),
                     }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            Purchase_date = i['Purchase_date']
            if type(Purchase_date) == type(0.01):
                d = handle_date(Purchase_date)
                Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            else:
                Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'], Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      Hard_disk=i['Hard_disk'], operating_system=i['operating_system'],
                                      IP_address=i['IP_address'], Wired_MAC_address=i['Wired_MAC_address'],
                                      CD_drive=i['CD_drive'], Purchase_date=Purchase_date,
                                      Condition_now=i['Condition_now'])
        os.remove("computer.xlsx")
def create_key_item_by_excel():
    """
    根据excel创造固定资产条目（key.xlsx）
    """
    if os.path.exists("key.xlsx"):
        data = xlrd.open_workbook('key.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'brand': "电子KEY",
                     'type': table.cell_value(row, 1),
                     'remarks': table.cell_value(row, 2), 'serial_number':table.cell_value(row, 3),
                     'Classified_level':table.cell_value(row, 4),'department': table.cell_value(row, 5),
                     'address': table.cell_value(row, 6), 'user': table.cell_value(row, 7),
                     'Condition_now': table.cell_value(row, 8),'Purchase_date': table.cell_value(row, 9),
                     }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            Purchase_date = i['Purchase_date']
            if type(Purchase_date) == type(0.01):
                d = handle_date(Purchase_date)
                Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            else:
                Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'], Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      Purchase_date=Purchase_date, Condition_now=i['Condition_now'])
        os.remove("key.xlsx")
def create_video_item_by_excel():
    """
    根据excel创造固定资产条目（video.xlsx）
    """
    if os.path.exists("video.xlsx"):
        data = xlrd.open_workbook('video.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'brand':table.cell_value(row, 2),
                     'type': table.cell_value(row, 1),
                     'Classified_level':table.cell_value(row, 3),'department': table.cell_value(row,4),
                     'address': table.cell_value(row, 5), 'user': table.cell_value(row, 6),
                     'serial_number': table.cell_value(row, 7),'remarks': table.cell_value(row, 8),
                     'Purchase_date': table.cell_value(row, 9),'Condition_now': table.cell_value(row, 10),
                     }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            Purchase_date = i['Purchase_date']
            if type(Purchase_date) == type(0.01):
                d = handle_date(Purchase_date)
                Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            else:
                Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'], Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      Purchase_date=Purchase_date, Condition_now=i['Condition_now'],
                                      serial_number=i['serial_number'])
        os.remove("video.xlsx")
def create__network_equipment_item_by_excel():
    """
    根据excel创造固定资产条目（network_equipment.xlsx）
    """
    if os.path.exists("network_equipment.xlsx"):
        data = xlrd.open_workbook('network_equipment.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'type': table.cell_value(row, 1),
                     'brand': table.cell_value(row, 2), 'Classified_level': table.cell_value(row, 3),
                     'address': table.cell_value(row, 5), 'user': table.cell_value(row, 6),
                     'serial_number': table.cell_value(row, 7),'remarks': table.cell_value(row, 8),
                     'Condition_now': table.cell_value(row, 9),'department': table.cell_value(row, 4),
                     'IP_address': table.cell_value(row, 10), 'Wired_MAC_address': table.cell_value(row, 11)
                    }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            # Purchase_date = i['Purchase_date']
            # if type(Purchase_date) == type(0.01):
            #     d = handle_date(Purchase_date)
            #     Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            # else:
            #     Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'],Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      IP_address=i['IP_address'], Wired_MAC_address=i['Wired_MAC_address'],
                                      Condition_now=i['Condition_now'],serial_number=i['serial_number'])
        os.remove("network_equipment.xlsx")
def create_printer_item_by_excel():
    """
    根据excel创造固定资产条目（printer.xlsx）
    """
    if os.path.exists("printer.xlsx"):
        data = xlrd.open_workbook('printer.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'brand':table.cell_value(row, 2),
                     'type': table.cell_value(row, 1),
                     'Classified_level':table.cell_value(row, 3),'department': table.cell_value(row,4),
                     'address': table.cell_value(row, 5), 'user': table.cell_value(row, 6),
                     'serial_number': table.cell_value(row, 7),'remarks': table.cell_value(row,9),
                     'Purchase_date': table.cell_value(row, 10),'Condition_now': table.cell_value(row, 8),
                     }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            Purchase_date = i['Purchase_date']
            if type(Purchase_date) == type(0.01):
                d = handle_date(Purchase_date)
                Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            else:
                Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'], Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      Purchase_date=Purchase_date, Condition_now=i['Condition_now'],
                                      serial_number=i['serial_number'])
        os.remove("printer.xlsx")
def create_software_item_by_excel():
    """
    根据excel创造固定资产条目（software.xlsx）
    """
    if os.path.exists("software.xlsx"):
        data = xlrd.open_workbook('software.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'brand': table.cell_value(row, 2),
                     'type': table.cell_value(row, 1),'department': table.cell_value(row, 3),
                     'user': table.cell_value(row, 4),'remarks':"应用范围:"+table.cell_value(row, 5),
                     'Condition_now': table.cell_value(row, 6)
                     }
            tables.append(array)
        tables.pop(0)
        for i in tables:
            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'],
                                      remarks=i['remarks'], department=i['department'],
                                      user=i['user'],
                                       Condition_now=i['Condition_now'])
        os.remove("software.xlsx")
def create_peripheral_item_by_excel():
    """
    根据excel创造固定资产条目（peripheral.xlsx）
    """
    if os.path.exists("peripheral.xlsx"):
        data = xlrd.open_workbook('peripheral.xlsx')
        table = data.sheets()[0]
        tables = []
        for row in range(table.nrows):
            array = {'Asset_Number': table.cell_value(row, 0), 'brand':table.cell_value(row, 2),
                     'type': table.cell_value(row, 1),
                     'Classified_level':table.cell_value(row, 3),'department': table.cell_value(row,4),
                     'address': table.cell_value(row, 5), 'user': table.cell_value(row, 6),
                     'serial_number': table.cell_value(row, 7),'remarks':"用途："+ table.cell_value(row,9),
                     'Purchase_date': table.cell_value(row, 10),'Condition_now': table.cell_value(row, 8),
                     }
            tables.append(array)
        tables.pop(0)

        for i in tables:
            Purchase_date = i['Purchase_date']
            if type(Purchase_date) == type(0.01):
                d = handle_date(Purchase_date)
                Purchase_date = str(d[0]) + "-" + str(d[1]) + "-" + str(d[2])
            else:
                Purchase_date = "0000-0-0"

            create_general_sheet_item(type=i['type'], brand=i['brand'],
                                      Asset_Number=i['Asset_Number'], Classified_level=i['Classified_level'],
                                      remarks=i['remarks'], department=i['department'],
                                      address=i['address'], user=i['user'],
                                      Purchase_date=Purchase_date, Condition_now=i['Condition_now'],
                                      serial_number=i['serial_number'])
        os.remove("peripheral.xlsx")
if __name__ == '__main__':
    create_peripheral_item_by_excel()
    create_software_item_by_excel()
    create_printer_item_by_excel()
    create__network_equipment_item_by_excel()
    create_video_item_by_excel()
    create_store_item_by_excel()
    create_computer_item_by_excel()
    create_key_item_by_excel()
    # show_general_sheet()
